package hu.blok1.veilingsite.server;

import hu.blok1.veilingsite.client.data.ProductService;
import hu.blok1.veilingsite.shared.Bid;
import hu.blok1.veilingsite.shared.Product;

import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.google.gwt.user.server.rpc.RemoteServiceServlet;

@SuppressWarnings("serial")
public class ProductServiceImpl extends RemoteServiceServlet implements
		ProductService {
	private int productID = 0;

	@Override
	public boolean createProduct(Product p) throws IllegalArgumentException {
		boolean returnBoolean = false;

		p.setProductID(getNumberOfProducts() + 1);
		p.setStatus("Active");
		p.setStartDate(new Date());

		try {
			String query = "INSERT INTO product (id, name, minimumprice, startdate, enddate, status, description, fk_user_id, category) VALUES ("
					+ p.getProductID()
					+ ", '"
					+ p.getName()
					+ "', "
					+ p.getMinimumPrize()
					+ ", to_date('"
					+ p.getStartDateString()
					+ "', 'dd-MM-yyyy hh24:mi:ss'), to_date('"
					+ p.getEndDateString()
					+ "', 'dd-MM-yyyy hh24:mi:ss'), '"
					+ p.getStatus()
					+ "', '"
					+ p.getDescription()
					+ "', "
					+ p.getUserID() + ", '" + p.getCategory() + "')";
			DBConnector.SendStatement(query);
			returnBoolean = true;
		} catch (Exception e) {
			System.out.println(e.getMessage());
		} finally {
			DBConnector.CloseStatement();
			DBConnector.CloseConnection();
		}
		return returnBoolean;
	}

	@Override
	public int getNumberOfProducts() throws IllegalArgumentException {
		String query = "SELECT MAX(id) AS \"highestID\" FROM product";
		try {
			ResultSet rs = DBConnector.GetResultSet(query);
			while (rs.next()) {
				productID = rs.getInt("highestID");
			}
		} catch (SQLException se) {
			System.out.println("SQL Exception: " + se.getMessage());
			System.out.println("SQLState: " + se.getSQLState());
			System.out.println("ErrorCode: " + se.getErrorCode());
			se.printStackTrace(System.out);
		} finally {
			DBConnector.CloseAll();
		}
		return productID;
	}

	@Override
	public List<Product> findMultiProduct() throws IllegalArgumentException {
		String query = "SELECT * FROM PRODUCT WHERE status = 'Active' ORDER BY enddate"; // DESC";

		List<Product> productList = new ArrayList<Product>();
		productList = GetProductObject.GetMultiProduct(DBConnector
				.GetResultSet(query));
		DBConnector.CloseAll();
		return productList;
	}

	@Override
	public Product findSingleProduct(int productID)
			throws IllegalArgumentException {
		String query = "SELECT * FROM PRODUCT WHERE id = " + productID;
		Product singleProduct = new Product();
		singleProduct = GetProductObject.GetSingleProduct(DBConnector
				.GetResultSet(query));
		try{
			String queryForUserName = "SELECT name FROM users WHERE id = " + singleProduct.getUserID() + "";
			ResultSet rsForUserName = DBConnector.GetResultSet(queryForUserName);
			while(rsForUserName.next()){
				singleProduct.setUserName(rsForUserName.getString("name"));
			}
			rsForUserName.close();
		}catch(SQLException e){
			e.printStackTrace();
		}
		DBConnector.CloseAll();
		return singleProduct;
	}

	@Override
	public List<Bid> findBidsOfProduct(int productID)
			throws IllegalArgumentException {
		String query = "SELECT id, bid, fk_user_id, bid_time FROM BIDS WHERE fk_product_id = "
				+ productID + " ORDER BY id DESC";
		List<Bid> bidsOfProduct = new ArrayList<Bid>();

		ResultSet rs = DBConnector.GetResultSet(query);
		try {
			while (rs.next()) {
				Bid b = new Bid();
				b.setBidID(rs.getInt("id"));
				b.setBid(rs.getInt("bid"));
				b.setUserID(rs.getInt("fk_user_id"));
				b.setBidTime(rs.getDate("bid_time"));
				
				String queryForUserName = "SELECT name FROM users WHERE id='" + b.getUserID() + "'";
				ResultSet rsForUserName = DBConnector.GetResultSet(queryForUserName);
				while(rsForUserName.next()){
					b.setUserName(rsForUserName.getString("name"));
				}
				rsForUserName.close();
				
				bidsOfProduct.add(b);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		DBConnector.CloseAll();
		return bidsOfProduct;
	}

	@Override
	public boolean createNewBid(Bid b) throws IllegalArgumentException {
		try {
			String query = "INSERT INTO bids (id, bid, fk_product_id, fk_user_id, bid_time) values ("
					+ (getHighestBidId() + 1)
					+ ", "
					+ b.getBid()
					+ ", "
					+ b.getProductID() + ", " + b.getUserID() + ", sysdate)";

			DBConnector.SendStatement(query);
			return true;
		} catch (Exception e) {
			System.out.println(e.getMessage());
			return false;
		} finally {
			DBConnector.CloseStatement();
			DBConnector.CloseConnection();
		}
	}

	@Override
	public int getHighestBid(int productID) throws IllegalArgumentException {
		String query = "SELECT MAX(bid) AS \"highestBid\" FROM bids WHERE fk_product_id = "
				+ productID;
		int highestBid = 0;
		try {
			ResultSet rs = DBConnector.GetResultSet(query);
			if(rs.first()) {
				highestBid = rs.getInt("highestBid");
			}
		} catch (SQLException se) {
			System.out.println("SQL Exception: " + se.getMessage());
			System.out.println("SQLState: " + se.getSQLState());
			System.out.println("ErrorCode: " + se.getErrorCode());
			se.printStackTrace(System.out);	
		} finally {
			DBConnector.CloseAll();
		}
		return highestBid;
	}

	public int getHighestBidId() {
		String query = "SELECT MAX(id) AS \"highestID\" FROM bids";
		int highestID = 0;
		try {
			ResultSet rs = DBConnector.GetResultSet(query);
			while (rs.next()) {
				highestID = rs.getInt("highestID");
			}
		} catch (SQLException se) {
			System.out.println("SQL Exception: " + se.getMessage());
			System.out.println("SQLState: " + se.getSQLState());
			System.out.println("ErrorCode: " + se.getErrorCode());
			se.printStackTrace(System.out);
		} finally {
			DBConnector.CloseAll();
		}
		return highestID;
	}
}